1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Imports System.Globalization
5
6 Public Class frmBilling
7 Dim st2 As String
8
9 Sub Reset()
10 txtCID.Text = ""
11 txtRemarks.Text = ""
12 txtCustomerName.Text = ""
13 txtAmount.Text = ""
14 txtCostPrice.Text = ""
15 txtCustomerID.Text = ""
16 txtDiscountAmount.Text = ""
17 txtDiscountPer.Text = ""
18 txtMargin.Text = ""
19 txtInvoiceNo.Text = ""
20 txtProductCode.Text = ""
21 txtProductName.Text = ""
22 txtQty.Text = ""
23 txtSellingPrice.Text = ""
24 txtTotalAmount.Text = ""
25 txtTotalQty.Text = ""
26 txtVAT.Text = ""
27 txtVATAmount.Text = ""
28 txtGrandTotal.Text = ""
29 txtTotalPayment.Text = ""
30 txtPaymentDue.Text = ""
31 dtpInvoiceDate.Value = Today
32 btnDelete.Enabled = False
33 btnUpdate.Enabled = False
34 btnSave.Enabled = True
35 btnRemove.Enabled = False
36 btnAdd.Enabled = True
37 btnRemove1.Enabled = False
38 btnAdd1.Enabled = True
39 btnPrint.Enabled = False
40 txtContactNo.ReadOnly = False
41 txtCustomerName.ReadOnly = False
42 txtContactNo.Text = ""
43 txtCustomerType.Text = ""
44 auto()
45 lblSet.Text = "Allowed"
46 DataGridView1.Rows.Clear()
47 DataGridView2.Rows.Clear()
48 Clear()
49 Clear1()
50 End Sub
51 Private Function GenerateID() As String
52 con = New SqlConnection(cs)
53 Dim value As String = "0000"
54 Try
55 ' Fetch the latest ID from the database
56 con.Open()
57 cmd = New SqlCommand("SELECT TOP 1 Inv_ID FROM InvoiceInfo ORDER BY Inv_ID DESC", con)
58 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
59 If rdr.HasRows Then
60 rdr.Read()
61 value = rdr.Item("Inv_ID")
62 End If
63 rdr.Close()
64 ' Increase the ID by 1
65 value += 1
66 ' Because incrementing a string with an integer removes 0's
67 ' we need to replace them. If necessary.
68 If value <= 9 Then 'Value is between 0 and 10
69 value = "000" & value
70 ElseIf value <= 99 Then 'Value is between 9 and 100
71 value = "00" & value
72 ElseIf value <= 999 Then 'Value is between 999 and 1000
73 value = "0" & value
74 End If
75 Catch ex As Exception
76 ' If an error occurs, check the connection state and close it if necessary.
77 If con.State = ConnectionState.Open Then
78 con.Close()
79 End If
80 value = "0000"
81 End Try
82 Return value
83 End Function
84 Sub auto()
85 Try
86 txtID.Text = GenerateID()
87 txtInvoiceNo.Text = "INV-" + GenerateID()
88 Catch ex As Exception
89 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
90 End Try
91 End Sub
92 Private Sub btnSelect_Click(sender As System.Object, e As System.EventArgs) Handles btnSelect.Click
93 frmCustomerRecord2.lblSet.Text = "Billing"
94 frmCustomerRecord2.lblUser.Text = lblUser.Text
95 frmCustomerRecord2.Reset()
96 frmCustomerRecord2.ShowDialog()
97 End Sub
98
99 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
100 Me.Close()
101 End Sub
102
103 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles btnSelectionInv.Click
104 frmCurrentStock.lblSet.Text = "Billing"
105 frmCurrentStock.Reset()
106 frmCurrentStock.ShowDialog()
107 End Sub
108 Sub Compute()
109 Dim num1, num2, num3, num4, num5 As Double
110 num1 = CDbl(Val(txtQty.Text) * Val(txtSellingPrice.Text))
111 num1 = Math.Round(num1, 2)
112 txtAmount.Text = num1
113 num2 = CDbl((Val(txtAmount.Text) * Val(txtDiscountPer.Text)) / 100)
114 num2 = Math.Round(num2, 2)
115 txtDiscountAmount.Text = num2
116 num3 = Val(txtAmount.Text) - Val(txtDiscountAmount.Text)
117 num4 = CDbl((Val(txtVAT.Text) * Val(num3)) / 100)
118 num4 = Math.Round(num4, 2)
119 txtVATAmount.Text = num4
120 num5 = CDbl(Val(txtAmount.Text) + Val(txtVATAmount.Text) - Val(txtDiscountAmount.Text))
121 num5 = Math.Round(num5, 2)
122 txtTotalAmount.Text = num5
123 End Sub
124
125 Private Sub txtQty_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtQty.TextChanged
126 Compute()
127 End Sub
128
129 Private Sub txtQty_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtQty.KeyPress
130 If (e.KeyChar < Chr(48) Or e.KeyChar > Chr(57)) And e.KeyChar <> Chr(8) Then
131 e.Handled = True
132 End If
133 End Sub
134 Public Function GrandTotal() As Double
135 Dim sum As Double = 0
136 Try
137 For Each r As DataGridViewRow In Me.DataGridView1.Rows
138 sum = sum + r.Cells(11).Value
139 Next
140 Catch ex As Exception
141 MsgBox(ex.Message)
142 End Try
143 Return sum
144 End Function
145 Public Function TotalPayment() As Double
146 Dim sum As Double = 0
147 Try
148 For Each r As DataGridViewRow In Me.DataGridView2.Rows
149 sum = sum + r.Cells(1).Value
150 Next
151 Catch ex As Exception
152 MsgBox(ex.Message)
153 End Try
154 Return sum
155 End Function
156 Sub Print()
157 Try
158 If txtCustomerType.Text <> "Non Regular" Then
159 Cursor = Cursors.WaitCursor
160 Timer1.Enabled = True
161 Dim rpt As New rptInvoice 'The report you created.
162 Dim myConnection As SqlConnection
163 Dim MyCommand, MyCommand1 As New SqlCommand()
164 Dim myDA, myDA1 As New SqlDataAdapter()
165 Dim myDS As New DataSet 'The DataSet you created.
166 myConnection = New SqlConnection(cs)
167 MyCommand.Connection = myConnection
168 MyCommand1.Connection = myConnection
169 MyCommand.CommandText = "SELECT Customer.ID, Customer.Name, Customer.Gender, Customer.Address, Customer.City, Customer.State, Customer.ZipCode, Customer.ContactNo, Customer.EmailID, Customer.Remarks,Customer.Photo, InvoiceInfo.Inv_ID, InvoiceInfo.InvoiceNo, InvoiceInfo.InvoiceDate, InvoiceInfo.CustomerID , InvoiceInfo.GrandTotal, InvoiceInfo.TotalPaid, InvoiceInfo.Balance, Invoice_Product.IPo_ID, Invoice_Product.InvoiceID, Invoice_Product.ProductID, Invoice_Product.CostPrice, Invoice_Product.SellingPrice, Invoice_Product.Margin,Invoice_Product.Qty, Invoice_Product.Amount, Invoice_Product.DiscountPer, Invoice_Product.Discount, Invoice_Product.VATPer, Invoice_Product.VAT, Invoice_Product.TotalAmount, Product.PID,Product.ProductCode, Product.ProductName FROM Customer INNER JOIN InvoiceInfo ON Customer.ID = InvoiceInfo.CustomerID INNER JOIN Invoice_Product ON InvoiceInfo.Inv_ID = Invoice_Product.InvoiceID INNER JOIN Product ON Invoice_Product.ProductID = Product.PID where InvoiceInfo.Invoiceno=@d1"
170 MyCommand.Parameters.AddWithValue("@d1", txtInvoiceNo.Text)
171 MyCommand1.CommandText = "SELECT * from Company"
172 MyCommand.CommandType = CommandType.Text
173 MyCommand1.CommandType = CommandType.Text
174 myDA.SelectCommand = MyCommand
175 myDA1.SelectCommand = MyCommand1
176 myDA.Fill(myDS, "InvoiceInfo")
177 myDA.Fill(myDS, "Invoice_Product")
178 myDA.Fill(myDS, "Customer")
179 myDA.Fill(myDS, "Product")
180 myDA1.Fill(myDS, "Company")
181 rpt.SetDataSource(myDS)
182 rpt.SetParameterValue("p1", txtCustomerID.Text)
183 rpt.SetParameterValue("p2", Today)
184 frmReport.CrystalReportViewer1.ReportSource = rpt
185 frmReport.ShowDialog()
186 End If
187 If txtCustomerType.Text = "Non Regular" Then
188 Cursor = Cursors.WaitCursor
189 Timer1.Enabled = True
190 Dim rpt As New rptInvoice2 'The report you created.
191 Dim myConnection As SqlConnection
192 Dim MyCommand, MyCommand1 As New SqlCommand()
193 Dim myDA, myDA1 As New SqlDataAdapter()
194 Dim myDS As New DataSet 'The DataSet you created.
195 myConnection = New SqlConnection(cs)
196 MyCommand.Connection = myConnection
197 MyCommand1.Connection = myConnection
198 MyCommand.CommandText = "SELECT Customer.ID, Customer.Name, Customer.Gender, Customer.Address, Customer.City, Customer.State, Customer.ZipCode, Customer.ContactNo, Customer.EmailID, Customer.Remarks,Customer.Photo, InvoiceInfo.Inv_ID, InvoiceInfo.InvoiceNo, InvoiceInfo.InvoiceDate, InvoiceInfo.CustomerID , InvoiceInfo.GrandTotal, InvoiceInfo.TotalPaid, InvoiceInfo.Balance, Invoice_Product.IPo_ID, Invoice_Product.InvoiceID, Invoice_Product.ProductID, Invoice_Product.CostPrice, Invoice_Product.SellingPrice, Invoice_Product.Margin,Invoice_Product.Qty, Invoice_Product.Amount, Invoice_Product.DiscountPer, Invoice_Product.Discount, Invoice_Product.VATPer, Invoice_Product.VAT, Invoice_Product.TotalAmount, Product.PID,Product.ProductCode, Product.ProductName FROM Customer INNER JOIN InvoiceInfo ON Customer.ID = InvoiceInfo.CustomerID INNER JOIN Invoice_Product ON InvoiceInfo.Inv_ID = Invoice_Product.InvoiceID INNER JOIN Product ON Invoice_Product.ProductID = Product.PID where InvoiceInfo.Invoiceno=@d1"
199 MyCommand.Parameters.AddWithValue("@d1", txtInvoiceNo.Text)
200 MyCommand1.CommandText = "SELECT * from Company"
201 MyCommand.CommandType = CommandType.Text
202 MyCommand1.CommandType = CommandType.Text
203 myDA.SelectCommand = MyCommand
204 myDA1.SelectCommand = MyCommand1
205 myDA.Fill(myDS, "InvoiceInfo")
206 myDA.Fill(myDS, "Invoice_Product")
207 myDA.Fill(myDS, "Customer")
208 myDA.Fill(myDS, "Product")
209 myDA1.Fill(myDS, "Company")
210 rpt.SetDataSource(myDS)
211 rpt.SetParameterValue("p1", txtCustomerID.Text)
212 rpt.SetParameterValue("p2", Today)
213 frmReport.CrystalReportViewer1.ReportSource = rpt
214 frmReport.ShowDialog()
215 End If
216 Catch ex As Exception
217 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
218 End Try
219
220 End Sub
221
222 Private Sub frmBilling_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
223
224 End Sub
225
226 Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
227 Try
228 If txtProductCode.Text = "" Then
229 MessageBox.Show("Please retrieve product code", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
230 txtProductCode.Focus()
231 Exit Sub
232 End If
233 If txtQty.Text = "" Then
234 MessageBox.Show("Please enter quantity", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
235 txtQty.Focus()
236 Exit Sub
237 End If
238 If txtQty.Text = 0 Then
239 MessageBox.Show("Quantity can not be zero", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
240 txtQty.Focus()
241 Exit Sub
242 End If
243 If DataGridView1.Rows.Count = 0 Then
244 DataGridView1.Rows.Add(txtProductCode.Text, txtProductName.Text, txtCostPrice.Text, txtSellingPrice.Text, txtMargin.Text, txtQty.Text, txtAmount.Text, txtDiscountPer.Text, txtDiscountAmount.Text, txtVAT.Text, txtVATAmount.Text, txtTotalAmount.Text, txtProductID.Text)
245 Dim k As Double = 0
246 k = GrandTotal()
247 k = Math.Round(k, 2)
248 txtGrandTotal.Text = k
249 Compute1()
250 Clear()
251 Exit Sub
252 End If
253 For Each r As DataGridViewRow In Me.DataGridView1.Rows
254 If r.Cells(0).Value = txtProductCode.Text Then
255 r.Cells(0).Value = txtProductCode.Text
256 r.Cells(1).Value = txtProductName.Text
257 r.Cells(2).Value = txtCostPrice.Text
258 r.Cells(3).Value = txtSellingPrice.Text
259 r.Cells(4).Value = txtMargin.Text
260 r.Cells(5).Value = Val(r.Cells(5).Value) + Val(txtQty.Text)
261 r.Cells(6).Value = Val(r.Cells(6).Value) + Val(txtAmount.Text)
262 r.Cells(7).Value = Val(txtDiscountPer.Text)
263 r.Cells(8).Value = Val(r.Cells(8).Value) + Val(txtDiscountAmount.Text)
264 r.Cells(9).Value = Val(txtVAT.Text)
265 r.Cells(10).Value = Val(r.Cells(10).Value) + Val(txtVATAmount.Text)
266 r.Cells(11).Value = Val(r.Cells(11).Value) + Val(txtTotalAmount.Text)
267 r.Cells(12).Value = txtProductID.Text
268 Dim i As Double = 0
269 i = GrandTotal()
270 i = Math.Round(i, 2)
271 txtGrandTotal.Text = i
272 Compute1()
273 Clear()
274 Exit Sub
275 End If
276 Next
277 DataGridView1.Rows.Add(txtProductCode.Text, txtProductName.Text, txtCostPrice.Text, txtSellingPrice.Text, txtMargin.Text, txtQty.Text, txtAmount.Text, txtDiscountPer.Text, txtDiscountAmount.Text, txtVAT.Text, txtVATAmount.Text, txtTotalAmount.Text, txtProductID.Text)
278 Dim j As Double = 0
279 j = GrandTotal()
280 j = Math.Round(j, 2)
281 txtGrandTotal.Text = j
282 Compute1()
283 Clear()
284 Catch ex As Exception
285 MsgBox(ex.Message)
286 End Try
287 End Sub
288 Sub Clear()
289 txtProductCode.Text = ""
290 txtProductName.Text = ""
291 txtCostPrice.Text = ""
292 txtSellingPrice.Text = ""
293 txtMargin.Text = ""
294 txtQty.Text = ""
295 txtAmount.Text = ""
296 txtDiscountPer.Text = ""
297 txtDiscountAmount.Text = ""
298 txtVAT.Text = ""
299 txtVATAmount.Text = ""
300 txtTotalAmount.Text = ""
301 btnAdd.Enabled = True
302 btnRemove.Enabled = False
303 btnListUpdate.Enabled = False
304 End Sub
305
306 Private Sub btnRemove_Click(sender As System.Object, e As System.EventArgs) Handles btnRemove.Click
307 Try
308 For Each row As DataGridViewRow In DataGridView1.SelectedRows
309 DataGridView1.Rows.Remove(row)
310 Next
311 Dim k As Double = 0
312 k = GrandTotal()
313 k = Math.Round(k, 2)
314 txtGrandTotal.Text = k
315 Compute()
316 Compute1()
317 Clear()
318 Catch ex As Exception
319 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
320 End Try
321 End Sub
322
323 Private Sub DataGridView1_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseClick
324
325 If (Me.DataGridView1.Rows.Count > 0) Then
326 If lblSet.Text = "Not Allowed" Then
327 btnRemove.Enabled = False
328 btnListUpdate.Enabled = False
329 Else
330 btnRemove.Enabled = True
331 btnListUpdate.Enabled = True
332 End If
333 Me.btnAdd.Enabled = False
334 Dim row As DataGridViewRow = Me.DataGridView1.SelectedRows.Item(0)
335 Me.txtProductCode.Text = (row.Cells.Item(0).Value)
336 Me.txtProductName.Text = (row.Cells.Item(1).Value)
337 Me.txtCostPrice.Text = (row.Cells.Item(2).Value)
338 Me.txtSellingPrice.Text = (row.Cells.Item(3).Value)
339 Me.txtMargin.Text = (row.Cells.Item(4).Value)
340 Me.txtQty.Text = (row.Cells.Item(5).Value)
341 Me.txtAmount.Text = (row.Cells.Item(6).Value)
342 Me.txtDiscountPer.Text = (row.Cells.Item(7).Value)
343 Me.txtDiscountAmount.Text = (row.Cells.Item(8).Value)
344 Me.txtVAT.Text = (row.Cells.Item(9).Value)
345 Me.txtVATAmount.Text = (row.Cells.Item(10).Value)
346 Me.txtTotalAmount.Text = (row.Cells.Item(11).Value)
347 Me.txtProductID.Text = (row.Cells.Item(12).Value)
348 End If
349 End Sub
350
351 Private Sub DataGridView1_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridView1.RowPostPaint
352 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
353 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
354 If DataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
355 DataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
356 End If
357 Dim b As Brush = SystemBrushes.ControlText
358 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
359
360 End Sub
361
362 Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
363 Try
364 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
365 DeleteRecord()
366 End If
367 Catch ex As Exception
368 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
369 End Try
370 End Sub
371 Private Sub DeleteRecord()
372
373 Try
374 Dim RowsAffected As Integer = 0
375 con = New SqlConnection(cs)
376 con.Open()
377 Dim cq As String = "delete from InvoiceInfo where Inv_ID=@d1"
378 cmd = New SqlCommand(cq)
379 cmd.Parameters.AddWithValue("@d1", txtID.Text)
380 cmd.Connection = con
381 RowsAffected = cmd.ExecuteNonQuery()
382 If RowsAffected > 0 Then
383 Dim st As String = "deleted the bill (Products) having invoice no. '" & txtInvoiceNo.Text & "'"
384 LogFunc(lblUser.Text, st)
385 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
386 Reset()
387 Else
388 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
389 Reset()
390 End If
391 If con.State = ConnectionState.Open Then
392 con.Close()
393
394 End If
395 Catch ex As Exception
396 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
397 End Try
398 End Sub
399 Sub Compute1()
400 Dim i As Double = 0
401 i = Val(txtGrandTotal.Text) - Val(txtTotalPayment.Text)
402 i = Math.Round(i, 2)
403 txtPaymentDue.Text = i
404 End Sub
405 Private Function GenerateID1() As String
406 con = New SqlConnection(cs)
407 Dim value As String = "0000"
408 Try
409 ' Fetch the latest ID from the database
410 con.Open()
411 cmd = New SqlCommand("SELECT TOP 1 ID FROM Customer ORDER BY ID DESC", con)
412 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
413 If rdr.HasRows Then
414 rdr.Read()
415 value = rdr.Item("ID")
416 End If
417 rdr.Close()
418 ' Increase the ID by 1
419 value += 1
420 ' Because incrementing a string with an integer removes 0's
421 ' we need to replace them. If necessary.
422 If value <= 9 Then 'Value is between 0 and 10
423 value = "000" & value
424 ElseIf value <= 99 Then 'Value is between 9 and 100
425 value = "00" & value
426 ElseIf value <= 999 Then 'Value is between 999 and 1000
427 value = "0" & value
428 End If
429 Catch ex As Exception
430 ' If an error occurs, check the connection state and close it if necessary.
431 If con.State = ConnectionState.Open Then
432 con.Close()
433 End If
434 value = "0000"
435 End Try
436 Return value
437 End Function
438 Sub auto1()
439 Try
440 txtCID.Text = GenerateID1()
441 txtCustomerID.Text = "C-" + GenerateID1()
442 Catch ex As Exception
443 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
444 End Try
445 End Sub
446 Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
447 If Len(Trim(txtCustomerName.Text)) = 0 Then
448 MessageBox.Show("Please retrieve or enter customer details", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
449 Exit Sub
450 End If
451 If Len(Trim(txtContactNo.Text)) = 0 Then
452 MessageBox.Show("Please Enter Contact No.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
453 txtContactNo.Focus()
454 Exit Sub
455 End If
456 If DataGridView1.Rows.Count = 0 Then
457 MessageBox.Show("sorry no product added to cart", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
458 Exit Sub
459 End If
460 If DataGridView2.Rows.Count = 0 Then
461 MessageBox.Show("sorry no payment info added to cart", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
462 Exit Sub
463 End If
464 If Val(txtTotalPayment.Text) > Val(txtGrandTotal.Text) Then
465 MessageBox.Show("Total payment can not be more than grand total", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
466 Exit Sub
467 End If
468 Try
469 con = New SqlConnection(cs)
470 con.Open()
471 Dim ctn1 As String = "select * from Company"
472 cmd = New SqlCommand(ctn1)
473 cmd.Connection = con
474 rdr = cmd.ExecuteReader()
475
476 If Not rdr.Read() Then
477 MessageBox.Show("Add company profile first in master entry", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
478 If (rdr IsNot Nothing) Then
479 rdr.Close()
480 End If
481 Return
482 End If
483 For Each row As DataGridViewRow In DataGridView1.Rows
484 Dim con As New SqlConnection(cs)
485 con.Open()
486 Dim cmd As New SqlCommand("SELECT Qty from Temp_Stock where ProductID=@d1", con)
487 cmd.Parameters.AddWithValue("@d1", row.Cells(12).Value.ToString())
488 Dim da As New SqlDataAdapter(cmd)
489 Dim ds As DataSet = New DataSet()
490 da.Fill(ds)
491 If ds.Tables(0).Rows.Count > 0 Then
492 txtTotalQty.Text = ds.Tables(0).Rows(0)("Qty")
493 If CInt(Val(row.Cells(5).Value)) > Val(txtTotalQty.Text) Then
494 MessageBox.Show("added qty. to cart are more than" & vbCrLf & "available qty. of product code '" & row.Cells(0).Value.ToString() & "' and Product Name='" & row.Cells(1).Value & "'", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
495 Exit Sub
496 End If
497 End If
498 con.Close()
499 Next
500 If txtCustomerName.ReadOnly = False Then
501 auto1()
502 con = New SqlConnection(cs)
503 con.Open()
504 Dim cbn As String = "insert into Customer(ID, CustomerID, [Name], Gender, Address, City, ContactNo, EmailID,Remarks,State,ZipCode,Photo,CustomerType) Values (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12,'Non Regular')"
505 cmd = New SqlCommand(cbn)
506 cmd.Parameters.AddWithValue("@d1", txtCID.Text)
507 cmd.Parameters.AddWithValue("@d2", txtCustomerID.Text)
508 cmd.Parameters.AddWithValue("@d3", txtCustomerName.Text)
509 cmd.Parameters.AddWithValue("@d4", "")
510 cmd.Parameters.AddWithValue("@d5", "")
511 cmd.Parameters.AddWithValue("@d6", "")
512 cmd.Parameters.AddWithValue("@d7", txtContactNo.Text)
513 cmd.Parameters.AddWithValue("@d8", "")
514 cmd.Parameters.AddWithValue("@d9", "")
515 cmd.Parameters.AddWithValue("@d10", "")
516 cmd.Parameters.AddWithValue("@d11", "")
517 cmd.Connection = con
518 Dim ms As New MemoryStream()
519 Dim bmpImage As New Bitmap(My.Resources.photo)
520 bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
521 Dim data As Byte() = ms.GetBuffer()
522 Dim p As New SqlParameter("@d12", SqlDbType.Image)
523 p.Value = data
524 cmd.Parameters.Add(p)
525 cmd.ExecuteNonQuery()
526 con.Close()
527 txtCustomerType.Text = "Non Regular"
528 End If
529
530 con = New SqlConnection(cs)
531 con.Open()
532 Dim cb As String = "insert into InvoiceInfo( Inv_ID, InvoiceNo, InvoiceDate, CustomerID, GrandTotal, TotalPaid, Balance, Remarks) Values (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8)"
533 cmd = New SqlCommand(cb)
534 cmd.Parameters.AddWithValue("@d1", txtID.Text)
535 cmd.Parameters.AddWithValue("@d2", txtInvoiceNo.Text)
536 cmd.Parameters.AddWithValue("@d3", dtpInvoiceDate.Value.Date)
537 cmd.Parameters.AddWithValue("@d4", txtCID.Text)
538 cmd.Parameters.AddWithValue("@d5", txtGrandTotal.Text)
539 cmd.Parameters.AddWithValue("@d6", txtTotalPayment.Text)
540 cmd.Parameters.AddWithValue("@d7", txtPaymentDue.Text)
541 cmd.Parameters.AddWithValue("@d8", txtRemarks.Text)
542 cmd.Connection = con
543 cmd.ExecuteReader()
544 con.Close()
545 con = New SqlConnection(cs)
546 con.Open()
547 Dim cb1 As String = "insert into Invoice_Product(InvoiceID, CostPrice, SellingPrice, Margin, Qty, Amount, DiscountPer, Discount, VATPer, VAT, TotalAmount,ProductID) VALUES (" & txtID.Text & " ,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12,@d13,@d14)"
548 cmd = New SqlCommand(cb1)
549 cmd.Connection = con
550 ' Prepare command for repeated execution
551 cmd.Prepare()
552 ' Data to be inserted
553 For Each row As DataGridViewRow In DataGridView1.Rows
554 If Not row.IsNewRow Then
555 cmd.Parameters.AddWithValue("@d4", row.Cells(2).Value)
556 cmd.Parameters.AddWithValue("@d5", row.Cells(3).Value)
557 cmd.Parameters.AddWithValue("@d6", row.Cells(4).Value)
558 cmd.Parameters.AddWithValue("@d7", row.Cells(5).Value)
559 cmd.Parameters.AddWithValue("@d8", row.Cells(6).Value)
560 cmd.Parameters.AddWithValue("@d9", row.Cells(7).Value)
561 cmd.Parameters.AddWithValue("@d10", row.Cells(8).Value)
562 cmd.Parameters.AddWithValue("@d11", row.Cells(9).Value)
563 cmd.Parameters.AddWithValue("@d12", row.Cells(10).Value)
564 cmd.Parameters.AddWithValue("@d13", row.Cells(11).Value)
565 cmd.Parameters.AddWithValue("@d14", row.Cells(12).Value)
566 cmd.ExecuteNonQuery()
567 cmd.Parameters.Clear()
568 End If
569 Next
570 con.Close()
571 con = New SqlConnection(cs)
572 con.Open()
573 Dim cb2 As String = "insert into Invoice_Payment(InvoiceID,PaymentMode,TotalPaid,PaymentDate) VALUES (" & txtID.Text & " ,@d4,@d5,@d6)"
574 cmd = New SqlCommand(cb2)
575 cmd.Connection = con
576 ' Prepare command for repeated execution
577 cmd.Prepare()
578 ' Data to be inserted
579 For Each row As DataGridViewRow In DataGridView2.Rows
580 If Not row.IsNewRow Then
581 cmd.Parameters.AddWithValue("@d4", row.Cells(0).Value)
582 cmd.Parameters.AddWithValue("@d5", row.Cells(1).Value)
583 cmd.Parameters.AddWithValue("@d6", row.Cells(2).Value)
584 cmd.ExecuteNonQuery()
585 cmd.Parameters.Clear()
586 End If
587 Next
588 con.Close()
589 For Each row As DataGridViewRow In DataGridView1.Rows
590 If Not row.IsNewRow Then
591 con = New SqlConnection(cs)
592 con.Open()
593 Dim cb4 As String = "update Temp_stock set qty = qty - (" & row.Cells(5).Value & ") where ProductID=@d1"
594 cmd = New SqlCommand(cb4)
595 cmd.Connection = con
596 cmd.Parameters.AddWithValue("@d1", row.Cells(12).Value)
597 cmd.ExecuteNonQuery()
598 con.Close()
599 End If
600 Next
601 con.Close()
602 Dim st As String = "added the new bill (Products) having invoice no. '" & txtInvoiceNo.Text & "'"
603 LogFunc(lblUser.Text, st)
604 If CheckForInternetConnection() = True Then
605 con = New SqlConnection(cs)
606 con.Open()
607 Dim ctn As String = "select RTRIM(APIURL) from SMSSetting where IsDefault='Yes' and IsEnabled='Yes'"
608 cmd = New SqlCommand(ctn)
609 cmd.Connection = con
610 rdr = cmd.ExecuteReader()
611 If rdr.Read() Then
612 st2 = rdr.GetValue(0)
613 Dim st3 As String = "Hello, " & txtCustomerName.Text & " you have successfully purchased the products having invoice no. " & txtInvoiceNo.Text & ""
614 SMSFunc(txtContactNo.Text, st3, st2)
615 If (rdr IsNot Nothing) Then
616 rdr.Close()
617 End If
618 End If
619 End If
620 con.Close()
621 btnSave.Enabled = False
622 RefreshRecords()
623 MessageBox.Show("Successfully done", "Billing", MessageBoxButtons.OK, MessageBoxIcon.Information)
624 Print()
625 Catch ex As Exception
626 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
627 End Try
628 End Sub
629
630 Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
631 If Len(Trim(txtCustomerName.Text)) = 0 Then
632 MessageBox.Show("Please retrieve customer details", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
633 Exit Sub
634 End If
635 If DataGridView1.Rows.Count = 0 Then
636 MessageBox.Show("sorry no product added to cart", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
637 Exit Sub
638 End If
639 If DataGridView2.Rows.Count = 0 Then
640 MessageBox.Show("sorry no payment info added to cart", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
641 Exit Sub
642 End If
643 If Val(txtTotalPayment.Text) > Val(txtGrandTotal.Text) Then
644 MessageBox.Show("Total payment can not be more than grand total", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
645 Exit Sub
646 End If
647 Try
648 con = New SqlConnection(cs)
649 con.Open()
650 Dim cb As String = "Update InvoiceInfo set InvoiceNo=@d2, CustomerID=@d4, GrandTotal=@d5, TotalPaid=@d6, Balance=@d7, Remarks=@d8 where INV_ID=@d1"
651 cmd = New SqlCommand(cb)
652 cmd.Parameters.AddWithValue("@d1", txtID.Text)
653 cmd.Parameters.AddWithValue("@d2", txtInvoiceNo.Text)
654 cmd.Parameters.AddWithValue("@d4", txtCID.Text)
655 cmd.Parameters.AddWithValue("@d5", txtGrandTotal.Text)
656 cmd.Parameters.AddWithValue("@d6", txtTotalPayment.Text)
657 cmd.Parameters.AddWithValue("@d7", txtPaymentDue.Text)
658 cmd.Parameters.AddWithValue("@d8", txtRemarks.Text)
659 cmd.Connection = con
660 cmd.ExecuteReader()
661 con.Close()
662 con = New SqlConnection(cs)
663 con.Open()
664 Dim cq As String = "delete from Invoice_Payment where InvoiceID=@d1"
665 cmd = New SqlCommand(cq)
666 cmd.Parameters.AddWithValue("@d1", txtID.Text)
667 cmd.Connection = con
668 cmd.ExecuteNonQuery()
669 con.Close()
670 con = New SqlConnection(cs)
671 con.Open()
672 Dim cb2 As String = "insert into Invoice_Payment(InvoiceID,PaymentMode,TotalPaid,PaymentDate) VALUES (" & txtID.Text & " ,@d4,@d5,@d6)"
673 cmd = New SqlCommand(cb2)
674 cmd.Connection = con
675 ' Prepare command for repeated execution
676 cmd.Prepare()
677 ' Data to be inserted
678 For Each row As DataGridViewRow In DataGridView2.Rows
679 If Not row.IsNewRow Then
680 cmd.Parameters.AddWithValue("@d4", row.Cells(0).Value)
681 cmd.Parameters.AddWithValue("@d5", row.Cells(1).Value)
682 cmd.Parameters.AddWithValue("@d6", row.Cells(2).Value)
683 cmd.ExecuteNonQuery()
684 cmd.Parameters.Clear()
685 End If
686 Next
687 con.Close()
688 Dim st As String = "updated the bill (Products) having invoice no. '" & txtInvoiceNo.Text & "'"
689 LogFunc(lblUser.Text, st)
690 btnUpdate.Enabled = False
691 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
692 Catch ex As Exception
693 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
694 End Try
695 End Sub
696
697 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
698 frmBillingRecord.Reset()
699 frmBillingRecord.ShowDialog()
700 End Sub
701
702 Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
703 Reset()
704 End Sub
705
706
707 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
708 Cursor = Cursors.Default
709 Timer1.Enabled = False
710 End Sub
711
712 Private Sub btnPrint_Click(sender As System.Object, e As System.EventArgs) Handles btnPrint.Click
713 Print()
714 End Sub
715
716 Private Sub btnAdd1_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd1.Click
717 Try
718 If DataGridView1.Rows.Count = 0 Then
719 MessageBox.Show("sorry no product added to cart", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
720 Exit Sub
721 End If
722 If cmbPaymentMode.Text = "" Then
723 MessageBox.Show("Please select payment mode", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
724 cmbPaymentMode.Focus()
725 Exit Sub
726 End If
727 If txtPayment.Text = "" Then
728 MessageBox.Show("Please enter payment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
729 txtPayment.Focus()
730 Exit Sub
731 End If
732 DataGridView2.Rows.Add(cmbPaymentMode.Text, txtPayment.Text, dtpPaymentDate.Value.Date)
733 Dim j As Double = 0
734 j = TotalPayment()
735 j = Math.Round(j, 2)
736 txtTotalPayment.Text = j
737 Compute1()
738 Clear1()
739 Catch ex As Exception
740 MsgBox(ex.Message)
741 End Try
742 End Sub
743 Sub Clear1()
744 cmbPaymentMode.SelectedIndex = -1
745 txtPayment.Text = ""
746 dtpPaymentDate.Text = Today
747 btnAdd1.Enabled = True
748 btnRemove1.Enabled = False
749 btnListUpdate1.Enabled = False
750 End Sub
751 Private Sub btnRemove1_Click(sender As System.Object, e As System.EventArgs) Handles btnRemove1.Click
752 Try
753 For Each row As DataGridViewRow In DataGridView2.SelectedRows
754 DataGridView2.Rows.Remove(row)
755 Next
756 Dim k As Double = 0
757 k = TotalPayment()
758 k = Math.Round(k, 2)
759 txtTotalPayment.Text = k
760 Compute1()
761 Compute()
762 Clear1()
763 Catch ex As Exception
764 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
765 End Try
766 End Sub
767
768 Private Sub txtPayment_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtPayment.KeyPress
769 Dim keyChar = e.KeyChar
770
771 If Char.IsControl(keyChar) Then
772 'Allow all control characters.
773 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
774 Dim text = Me.txtPayment.Text
775 Dim selectionStart = Me.txtPayment.SelectionStart
776 Dim selectionLength = Me.txtPayment.SelectionLength
777
778 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
779
780 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
781 'Reject an integer that is longer than 16 digits.
782 e.Handled = True
783 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
784 'Reject a real number with two many decimal places.
785 e.Handled = False
786 End If
787 Else
788 'Reject all other characters.
789 e.Handled = True
790 End If
791 End Sub
792
793 Private Sub DataGridView2_MouseClick(sender As System.Object, e As System.Windows.Forms.MouseEventArgs) Handles DataGridView2.MouseClick
794 btnRemove1.Enabled = True
795 If (Me.DataGridView2.Rows.Count > 0) Then
796 Me.btnRemove1.Enabled = True
797 Me.btnListUpdate1.Enabled = True
798 Me.btnAdd1.Enabled = False
799 Dim row As DataGridViewRow = Me.DataGridView2.SelectedRows.Item(0)
800 Me.cmbPaymentMode.Text = (row.Cells.Item(0).Value)
801 Me.txtPayment.Text = (row.Cells.Item(1).Value)
802 Me.dtpPaymentDate.Text = (row.Cells.Item(2).Value)
803 End If
804 End Sub
805
806 Private Sub btnListReset1_Click(sender As System.Object, e As System.EventArgs) Handles btnListReset1.Click
807 Clear1()
808 End Sub
809
810 Private Sub btnListReset_Click(sender As System.Object, e As System.EventArgs) Handles btnListReset.Click
811 Clear()
812 End Sub
813
814 Private Sub DataGridView2_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridView2.RowPostPaint
815 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
816 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
817 If DataGridView2.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
818 DataGridView2.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
819 End If
820 Dim b As Brush = SystemBrushes.ControlText
821 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
822
823 End Sub
824
825 Private Sub btnListUpdate1_Click(sender As System.Object, e As System.EventArgs) Handles btnListUpdate1.Click
826 Try
827 If DataGridView1.Rows.Count = 0 Then
828 MessageBox.Show("sorry no product added to cart", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
829 Exit Sub
830 End If
831 If cmbPaymentMode.Text = "" Then
832 MessageBox.Show("Please select payment mode", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
833 cmbPaymentMode.Focus()
834 Exit Sub
835 End If
836 If txtPayment.Text = "" Then
837 MessageBox.Show("Please enter payment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
838 txtPayment.Focus()
839 Exit Sub
840 End If
841 For Each row As DataGridViewRow In DataGridView2.SelectedRows
842 DataGridView2.Rows.Remove(row)
843 Next
844 DataGridView2.Rows.Add(cmbPaymentMode.Text, txtPayment.Text, dtpPaymentDate.Value.Date)
845 Dim j As Double = 0
846 j = TotalPayment()
847 j = Math.Round(j, 2)
848 txtTotalPayment.Text = j
849 Compute1()
850 Clear1()
851 Catch ex As Exception
852 MsgBox(ex.Message)
853 End Try
854 End Sub
855
856 Private Sub btnListUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnListUpdate.Click
857 Try
858 If txtProductCode.Text = "" Then
859 MessageBox.Show("Please retrieve product code", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
860 txtProductCode.Focus()
861 Exit Sub
862 End If
863 If txtQty.Text = "" Then
864 MessageBox.Show("Please enter quantity", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
865 txtQty.Focus()
866 Exit Sub
867 End If
868 If txtQty.Text = 0 Then
869 MessageBox.Show("Quantity can not be zero", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
870 txtQty.Focus()
871 Exit Sub
872 End If
873
874 For Each row As DataGridViewRow In DataGridView1.SelectedRows
875 DataGridView1.Rows.Remove(row)
876 Next
877 DataGridView1.Rows.Add(txtProductCode.Text, txtProductName.Text, txtCostPrice.Text, txtSellingPrice.Text, txtMargin.Text, txtQty.Text, txtAmount.Text, txtDiscountPer.Text, txtDiscountAmount.Text, txtVAT.Text, txtVATAmount.Text, txtTotalAmount.Text, txtProductID.Text)
878 Dim k As Double = 0
879 k = GrandTotal()
880 k = Math.Round(k, 2)
881 txtGrandTotal.Text = k
882 Compute1()
883 Clear()
884 Catch ex As Exception
885 MsgBox(ex.Message)
886 End Try
887 End Sub
888 End Class
